"""管道文件，用于处理数据"""
# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import datetime
import json

import pymysql
import uuid
import xlwt
import pandas as pd


class HospitalspiderPipeline:
    # 初始化函数
    def __init__(self):
        # 创建xls文件，设置表头
        self.fileName = "医院信息.xls"
        self.book = xlwt.Workbook(encoding="utf8")
        self.sheet = self.book.add_sheet("sheet1")
        heads = ["内容", "链接"]
        self.num = 1  # 控制行，从第二行开始写
        for head in heads:
            self.sheet.write(0, heads.index(head), head)

    # 在此函数中操作数据
    def process_item(self, item, spider):
        print(item, "++++++++++")
        # j = 0  # 控制列
        # for k, v in item.items():
        #     self.sheet.write(self.num, j, v)
        #     j += 1
        # self.num += 1
        # return item
        pass

    # 爬虫关闭时候执行此函数，保存文件到磁盘
    def close_spider(self, spider):
        pass
        # self.book.save(self.fileName)


# 此pipelines用来处理数据并切实现入库
class CrawlHospitalPipline:
    def __init__(self):
        # 连接MySQL数据库
        self.connect = pymysql.connect(host='124.71.29.162', user='root', password='St1282001316*',
                                       db='scrapy_hospital',
                                       port=3306)
        self.cursor = self.connect.cursor()
        print("数据库连接成功")

    def process_item(self, item, spider):
        # 往数据库里面写入数据

        self.dealItem(item)
        print(item)

        sql = 'INSERT INTO t_nmd_mp_hospital (c_id,c_hospital_name,c_province,c_city,c_area,c_level_one,c_level_two,' \
              'd_create_date,n_type,n_medicine_insurance, n_ownership_type,c_phone,c_dean,c_address,c_introduction, ' \
              'n_create_id, dt_create_time) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        # 济源的和神农架的不入库
        # if "济源" not in item["c_city"] and "神农架" not in item["c_city"]:
        self.cursor.execute(sql,
                            (item["c_id"], item["c_hospital_name"], item['c_province'], item['c_city'], item['c_area'],
                             item['c_level_one'],
                             item['c_level_two'], item['d_create_date'],
                             item['n_type'], item['n_medicine_insurance'], item['n_ownership_type'], item['c_phone'],
                             item['c_dean'], item['c_address'], item['c_introduction'], item["n_create_id"],
                             item["dt_create_time"]))
        print("插入数据成功")
        self.connect.commit()
        return item

    # 生成id
    def generateId(self):
        return str(uuid.uuid1()).replace("-", "")

    def dealItem(self, item):
        # 设置id
        item["c_id"] = self.generateId()
        level = item["level"]
        # 医院等级设置
        if len(level) == 4:
            levelOne = level[0:2]
            levelTwo = level[2:4]
            if levelOne == "一级":
                item["c_level_one"] = "1"
            elif levelOne == "二级":
                item["c_level_one"] = "2"
            elif levelOne == "三级":
                item["c_level_one"] = "3"
            if levelTwo == "特等":
                item["c_level_two"] = "0"
            elif levelTwo == "甲等":
                item["c_level_two"] = "1"
            elif levelOne == "乙等":
                item["c_level_two"] = "2"
            elif levelOne == "丙等":
                item["c_level_two"] = "3"
            else:
                item["c_level_two"] = None
        elif level == "未知" or level == "未评级":
            item["c_level_one"] = "0"
            item["c_level_two"] = None
        # del item["level"]
        # 设置地址
        if item['c_address'] is None:
            item['c_address'] = "-"
        # 设置医院是公立还是私立 1-公立  2-私立
        public = item["n_ownership_type"]
        if public == "公立":
            item["n_ownership_type"] = 2
        else:
            item["n_ownership_type"] = 1
        # 设置电话
        if item["c_phone"] is None:
            item["c_phone"] = "暂无电话"
        # 设置 医院类型 1-综合医院  2-专科医院   3-其他
        type = item["n_type"]
        if type == "综合医院":
            item["n_type"] = 1
        elif type == "专科医院":
            item["n_type"] = 2
        else:
            item["n_type"] = 3
        # 设置建院时间
        createYear = item["createYear"]
        if createYear != "" and createYear != None and createYear != "-":
            createYear = createYear.replace("年", "")
            item["d_create_date"] = datetime.date(int(createYear), 1, 1).strftime("%Y-%m-%d")
        else:
            item["d_create_date"] = None
        del item["createYear"]
        # 设置是否医保
        if item["yibao"] == "医保":
            item["n_medicine_insurance"] = "1"
        else:
            item["n_medicine_insurance"] = "2"
        del item["yibao"]
        # 设置简介
        if len(item['c_introduction']) > 1000:
            item['c_introduction'] = item['c_introduction'][0:1000]
        # 设置 创建人 创建时间
        item["n_create_id"] = 108
        item["dt_create_time"] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        # 设置省，市，区
        province = item["c_province"]
        city = str(item["c_city"])[0:len(str(item["c_city"])) - 1]
        area = str(item["c_area"])[0:len(str(item["c_area"])) - 1]
        with open("E:\PycharmProjects\hospitalSpider\province.json", "r", encoding="UTF-8") as json_object:
            list = json.load(json_object)
            flag = False
            for i in list:
                if province == i["label"]:
                    item["c_province"] = i["value"]
                    for j in i["children"]:
                        if "children" in j.keys():
                            for k in j["children"]:
                                if area in k["label"] or city in k["label"]:
                                    item["c_area"] = k["value"]
                                    item["c_city"] = j["value"]
                                    flag = True
                                    break
                        if flag:
                            break
                if flag:
                    break

    # 关闭数据库
    def close_spider(self, spider):
        self.cursor.close()
        self.connect.close()


#    此pipeline用与把爬去的数据存入excel
class CrawlHospitalToExcelPipeline:
    def __init__(self):
        self.tables = []

    def process_item(self, item, spider):
        level = item["level"]
        # 医院等级设置
        if len(level) == 4:
            item["c_level_one"] = level[0:2]
            item["c_level_two"] = level[2:4]
        elif level == "未知" or level == "未评级":
            item["c_level_one"] = "未定级"
            item["c_level_two"] = ""
        del item["level"]
        # 设置地址
        if item['c_address'] is None:
            item['c_address'] = "-"
        # 设置电话
        if item["c_phone"] is None:
            item["c_phone"] = "-"
        # 设置 医院类型 1-综合医院  2-专科医院   3-其他
        # 设置建院时间
        createYear = item["createYear"]
        if createYear != "" and createYear != None and createYear != "-":
            createYear = createYear.replace("年", "")
            if createYear != "" and createYear != None:
                if len(createYear) > 4:
                    createYear = createYear[0:4]
                try:
                    item["createYear"] = datetime.date(int(createYear), 1, 1).strftime("%Y-%m-%d")
                except:
                    item["createYear"] = ""
            else:
                item["createYear"] = ""
        else:
            item["createYear"] = ""
        # 设置是否医保
        if item["yibao"] == "医保":
            item["n_medicine_insurance"] = "是"
        else:
            item["n_medicine_insurance"] = "否"
        del item["yibao"]
        # 设置简介
        if len(item['c_introduction']) > 1000:
            item['c_introduction'] = item['c_introduction'][0:1000]
        item["xzdj"] = ""
        item["c_remark"] = ""
        item["c_legel_person"] = ""
        # print(item["c_hospital_name"])
        print(item["c_hospital_name"])
        self.tables.append(item)

    def export_excel(self):
        # 将字典列表转换为DataFrame
        pf = pd.DataFrame(list(self.tables))
        order = [
            'c_hospital_name', 'c_province', 'c_city', 'c_area', 'createYear', 'c_level_one', 'c_level_two', 'xzdj',
            'n_type',
            'n_medicine_insurance', 'n_ownership_type', 'c_legel_person', 'c_dean', 'c_phone', 'c_address', 'c_introduction',
            'c_remark'
        ]
        pf = pf[order]
        # 将列名替换为中文
        columns_map = {
            'c_hospital_name': '医院名称',
            'c_province': '省',
            'c_city': '市',
            'c_area': '区',
            'createYear': '成立时间',
            'c_level_one': '医院等级一',
            'c_level_two': '医院等级二',
            'xzdj': "行政等级",   #字典中没有这个
            'n_type': '医院类型',
            'n_medicine_insurance': '医保定点',
            'n_ownership_type': '所有制类型',
            'c_legel_person': '法人',  #字典中没有这个
            'c_dean': '院长',
            'c_phone': '办公电话',
            'c_address': '详细地址',
            'c_introduction': '简介',
            'c_remark': '备注', #字典中没有这个
        }
        pf.rename(columns=columns_map, inplace=True)
        # 指定生成的Excel表格名称
        file_path = pd.ExcelWriter('hospital.xlsx')
        # 替换空单元格
        pf.fillna(' ', inplace=True)
        # 输出
        pf.to_excel(file_path, encoding='utf-8', index=False)
        # 保存表格
        file_path.save()

    # 关闭爬虫 的时候把数据写入excel 并保存
    def close_spider(self, spider):
        self.export_excel()
        print("爬取，写入完成！")

